package com.utils.report;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
import net.sf.jxls.util.Util;

/**
 * excel操作工具类
 * @author mapei
 *
 */
public class ExcelUtil {
	/**
	 * poi给某个单元格写值
	 * @param sheet
	 * @param r 行号 从0开始算
	 * @param c 列号
	 * @param Value
	 */
//	public static void poiSetCellVal(Sheet sheet,int r, int c,String Value){
//		Row row = sheet.getRow(r);
//		if(row != null){
//			Cell cell = row.getCell(c);
//			if(cell!=null){
//				cell.setCellValue(Value);
//			}
//		}
//	}
	public static void poiSetCellVal(Sheet sheet,int r, int c,String Value){
		Row row = null;
		Cell cell = null;
		if(sheet.getRow(r)==null){
			row = sheet.createRow(r);
			if(row.getCell(c) == null){
				cell = row.createCell(c);
			}else{
				cell = row.getCell(c);
			}
			cell.setCellValue(Value);
		}else{
			row = sheet.getRow(r);
			if(row.getCell(c) == null){
				cell = row.createCell(c);
			}else{
				cell = row.getCell(c);
			}
			cell.setCellValue(Value);
		}
	}
	/**
	 * poi隐藏行
	 * @param sheet
	 * @param s
	 * @param e
	 */
	public static void poiHideRow(Sheet sheet,int s,int e){
		HSSFRow row1=null;
		for(int r = s;r<=e;r++){
			row1 = (HSSFRow) sheet.getRow(r);
			if(row1!=null){
				row1.setZeroHeight(true);
			}
		}
	}
	
	public static StringBuffer ExcelDataToJson(File file) {
		InputStream is;
		try {
			is = new FileInputStream(file);
			return ExcelDataToJson(is);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static StringBuffer ExcelDataToJson(InputStream is) {
		return arrayToJson(getExcelData(is));
	}

	public static String[][] getExcelData(InputStream is) {
		if (is == null) {
			return null;
		}
		String[][] data = null;
		try {
			HSSFWorkbook book = new HSSFWorkbook(is);
			HSSFSheet sheet = book.getSheetAt(0);
			int rows = sheet.getLastRowNum();
			for (int i = 0; i < rows; i++) {
				HSSFRow row = sheet.getRow(i);
				int cols = row.getLastCellNum();
				if(i == 0) {
					data = new String[rows][cols];
				}
				for (int j = 0; j < cols; j++) {
					HSSFCell cell = row.getCell(j);
//					System.out.println("" + i + " " + j +":"+cell);
					data[i][j] = getCellValue(cell);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return data;
	}

	public static StringBuffer arrayToJson(String[][] srcArray) {
		if (srcArray == null) {
			return null;
		}
		StringBuffer sb = new StringBuffer();
		sb.append("[");
		int l = srcArray.length;
		for (int i = 0; i < l; i++) {
			String[] item = srcArray[i];
			if (i > 0) {
				sb.append(",");
			}
			sb.append("{");
			for (int j = 0, l2 = item.length; j < l2; j++) {
				if (j > 0) {
					sb.append(",");
				}
				sb.append("\"col" + j + "\":\"" + item[j] + "\"");
			}
			sb.append("}");

		}
		sb.append("]");

		StringBuffer sb2 = new StringBuffer();
		sb2.append("{");
		sb2.append("\"total\":" + l + ", \"rows\":");
		sb2.append(sb);
		sb2.append("}");

		return sb2;
	}
	
	public static void jsonToExcel(String text, String srcPath,
			HttpServletResponse response, String fileName)
			throws ParsePropertyException, InvalidFormatException {
		InputStream is;
		try {
			is = new FileInputStream(srcPath);
			Workbook workbook = jsonToExcel(text, is);
			saveWorkbook(workbook, response, fileName);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void jsonToExcel(String text, InputStream is,
			HttpServletResponse response, String fileName)
			throws ParsePropertyException, InvalidFormatException {
		Workbook workbook = jsonToExcel(text, is);
		saveWorkbook(workbook, response, fileName);
	}

	public static Workbook jsonToExcel(String text, InputStream is)
			throws ParsePropertyException, InvalidFormatException {
		JSONObject jsonObj = JSON.parseObject(text);

		Workbook workbook = jsonToExcel(jsonObj, is);

		return workbook;
	}

	public static Workbook jsonToExcel(JSONObject jsonObj, InputStream is)
			throws ParsePropertyException, InvalidFormatException {
		Map beans = new HashMap();
		beans.put("T", jsonObj);

		XLSTransformer transformer = new XLSTransformer();
//		transformer.markAsFixedSizeCollection("T");
		Workbook workbook = transformer.transformXLS(is, beans);

		return workbook;
	}

	/**
	 * 动态sheet生成
	 */
	public static Workbook jsonToExcel_MultipleSheets(InputStream is, String text, String listName, String sheetName, String beanName)
			throws ParsePropertyException, InvalidFormatException {
		JSONObject jsonObj = JSON.parseObject(text);
		Workbook workbook = jsonToExcel_MultipleSheets(is, jsonObj, listName, sheetName, beanName);
		return workbook;
	}
	
	/**
	 * 动态sheet生成
	 */
	public static Workbook jsonToExcel_MultipleSheets(InputStream is, JSONObject jsonObj, String listName, String sheetName, String beanName)
			throws ParsePropertyException, InvalidFormatException {
		JSONArray beans = jsonObj.getJSONArray(listName);
		
		List sheetNames = new ArrayList();
		for (int i = 0; i < beans.size(); i++) {
			JSONObject bean = (JSONObject) beans.get(i);
			String sname =  (String) bean.get(sheetName);
			int idx = sheetNames.indexOf(sname);
			if(idx!=-1){
				sname = sname + "1";
			}
			sheetNames.add(sname);
		}
		
		jsonObj.remove(listName);
		
		Map beanParams = new HashMap();
		beanParams.put("T", jsonObj);
			
		XLSTransformer transformer = new XLSTransformer();
		Workbook workbook = transformer.transformMultipleSheetsList(is, beans, sheetNames, beanName, beanParams, 0);
		
		return workbook;
	}

	/**
	 * 动态sheet生成
	 */
	public static Workbook jsonToExcel_MultipleSheets2(InputStream is, String jsonStr, String listKey, String sheetNameKey, String beanName)
			throws ParsePropertyException, InvalidFormatException {
		JSONObject jsonObj = JSON.parseObject(jsonStr);
		Workbook workbook = jsonToExcel_MultipleSheets2(is, jsonObj, listKey, sheetNameKey, beanName);
		return workbook;
	}

	/**
	 * 动态sheet生成
	 */
	public static Workbook jsonToExcel_MultipleSheets2(InputStream is, JSONObject jsonObj, String listKey, String sheetNameKey, String beanName)
			throws ParsePropertyException, InvalidFormatException {
		JSONArray beans = jsonObj.getJSONArray(listKey);
		jsonObj.remove(listKey);
		Map beanParams = new HashMap();
		beanParams.put("T", jsonObj);
		Workbook workbook = transformMultipleSheetsList(is, beans, sheetNameKey, beanName, beanParams, 0);
		return workbook;
	}

	/**
	 * 动态sheet生成
	 */
	public static Workbook transformMultipleSheetsList(InputStream is, JSONArray objects, String sheetNameKey, String beanName, Map beanParams, int startSheetNum) 
			throws ParsePropertyException, InvalidFormatException {
		XLSTransformer transformer = new XLSTransformer();
		org.apache.poi.ss.usermodel.Workbook hssfWorkbook = null;
        try {
            if( beanParams!=null && beanParams.containsKey( beanName )){
                throw new IllegalArgumentException("Selected bean name '" + beanName + "' already exists in the bean map");
            }
            if( beanName==null ){
                throw new IllegalArgumentException(("Bean name must not be null" ) );
            }
            if( beanParams == null ){
                beanParams = new HashMap();
            }
            hssfWorkbook = WorkbookFactory.create(is);
            Map sheetNames = new HashMap();
            for (int sheetNo = 0; sheetNo < hssfWorkbook.getNumberOfSheets(); sheetNo++) {
                final String spreadsheetName = hssfWorkbook.getSheetName(sheetNo);
                    org.apache.poi.ss.usermodel.Sheet hssfSheet = hssfWorkbook.getSheetAt(sheetNo);
                    if( startSheetNum == sheetNo && objects != null && !objects.isEmpty()){
                        for (int i = 0, c2 = objects.size(); i < c2 ; i++) {
                        	JSONObject bean =  (JSONObject) objects.get(i);
                            String beanKey = beanName;
                            org.apache.poi.ss.usermodel.Sheet newSheet;
                            String sheetName = (String) bean.get(sheetNameKey);
                        	sheetName = rename(sheetNames, sheetName);
                            sheetNames.put(sheetName, sheetName);
                            if( i != 0 ){
                                beanKey = beanName+i;
                                newSheet = hssfWorkbook.createSheet( sheetName );
                                Util.copySheets(newSheet, hssfSheet, beanName, beanKey );
								copyPageSetup(newSheet, hssfSheet);
								copyPrintSetup(newSheet, hssfSheet);
								copyPrintMargin(newSheet, hssfSheet);
                            }else{
                                hssfWorkbook.setSheetName( sheetNo, sheetName);
                            }
                            beanParams.put( beanKey, bean );
                        }
                    }else{
                    }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
		transformer.transformWorkbook(hssfWorkbook, beanParams);
		if (hssfWorkbook != null) {
			for (int i = 0; i < hssfWorkbook.getNumberOfSheets(); i++) {
				setPrintArea(hssfWorkbook, i);
			}
		}

        return hssfWorkbook;
	}
	
	/**
	 * 重名
	 * @param sheetNames
	 * @param sheetName
	 * @return
	 */
	public static String rename(Map sheetNames, String sheetName) {
		if (sheetNames.containsKey(sheetName)) {
			String append = "_2";
			int len = sheetName.length();
			int idx = sheetName.lastIndexOf("_");
			if (idx != -1 && idx < len - 1) {
				String num = sheetName.substring(idx + 1, len);
				try {
					int no = Integer.parseInt(num);
					append = "_" + (no + 1);
					sheetName = sheetName.substring(0, idx);
				} catch (Exception e) {
				} finally {
				}
			}
			sheetName = sheetName + append;
			sheetName = rename(sheetNames, sheetName);
		}
		return sheetName;
	}
	

    /**
     * 页面设置--打印区域
     * @param resultWorkbook
     * @param sheetNum
     */
	public static void setPrintArea(Workbook resultWorkbook, int sheetNum) {
		int maxColumnNum = 0;
		int lastRowNum = resultWorkbook.getSheetAt(sheetNum).getLastRowNum();
		for (int j = resultWorkbook.getSheetAt(sheetNum).getFirstRowNum(); j <= lastRowNum; j++) {
			Row row = resultWorkbook.getSheetAt(sheetNum).getRow(j);
			if (row != null) {
				maxColumnNum = row.getLastCellNum();
			}
		}
		if(maxColumnNum > 1){
			maxColumnNum = maxColumnNum - 1;
		}
		resultWorkbook.setPrintArea(sheetNum, 0, maxColumnNum, 0, lastRowNum);
	}


	/**
	 * 页面设置--页眉/页脚
	 * @param destSheet
	 * @param srcSheet
	 */
	public static void copyPageSetup(Sheet destSheet, Sheet srcSheet) {
		Header header = srcSheet.getHeader();
		Footer footer = srcSheet.getFooter();
		if (footer != null) {
			destSheet.getFooter().setLeft(footer.getLeft());
			destSheet.getFooter().setCenter(footer.getCenter());
			destSheet.getFooter().setRight(footer.getRight());
		}
		if (header != null) {
			destSheet.getHeader().setLeft(header.getLeft());
			destSheet.getHeader().setCenter(header.getCenter());
			destSheet.getHeader().setRight(header.getRight());
		}
	}

	/**
	 * 页面设置--页面
	 * @param destSheet
	 * @param srcSheet
	 */
	public static void copyPrintSetup(Sheet destSheet, Sheet srcSheet) {
		PrintSetup setup = srcSheet.getPrintSetup();
		if (setup != null) {
			destSheet.getPrintSetup().setLandscape(setup.getLandscape());//方向，true:横向，false:纵向
			
			destSheet.getPrintSetup().setScale(setup.getScale());//缩放比例
			destSheet.getPrintSetup().setFitWidth(setup.getFitWidth());
			destSheet.getPrintSetup().setFitHeight(setup.getFitHeight());

			destSheet.getPrintSetup().setPaperSize(setup.getPaperSize());//纸张大小
			
			destSheet.getPrintSetup().setPageStart(setup.getPageStart());//起始页码
			
			destSheet.getPrintSetup().setFooterMargin(setup.getFooterMargin());//页脚边距
			destSheet.getPrintSetup().setHeaderMargin(setup.getHeaderMargin());//页眉边距
		}
	}

	/**
	 * 页面设置--页边距
	 * @param destSheet
	 * @param srcSheet
	 */
	public static void copyPrintMargin(Sheet destSheet, Sheet srcSheet) {
//		destSheet.setMargin(destSheet.FooterMargin, srcSheet.getMargin(srcSheet.FooterMargin));//页眉边距
//		destSheet.setMargin(destSheet.HeaderMargin, srcSheet.getMargin(srcSheet.HeaderMargin));//页脚边距
		
		destSheet.setMargin(destSheet.TopMargin, srcSheet.getMargin(srcSheet.TopMargin));//页边距（上）
		destSheet.setMargin(destSheet.BottomMargin, srcSheet.getMargin(srcSheet.BottomMargin));//页边距（下）
		destSheet.setMargin(destSheet.LeftMargin, srcSheet.getMargin(srcSheet.LeftMargin));//页边距（左）
		destSheet.setMargin(destSheet.RightMargin, srcSheet.getMargin(srcSheet.RightMargin));//页边距（右）
		
		// 居中方式
		destSheet.setHorizontallyCenter(srcSheet.getHorizontallyCenter());//水平
		destSheet.setVerticallyCenter(srcSheet.getVerticallyCenter());//垂直
	}

	private static void saveWorkbook(Workbook resultWorkbook,
			HttpServletResponse response, String fileName) {
		try {
			response.setHeader("content-disposition", "attachment; filename="
					+ fileName);
			response.setContentType("application/msexcel");
			OutputStream os = response.getOutputStream();
			resultWorkbook.write(os);
			os.flush();
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取列数
	 * @param file
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static int getHeadCols(File file)throws FileNotFoundException, IOException{
		int col = 0;
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		if(wb.getNumberOfSheets()>0){
			HSSFSheet st = wb.getSheetAt(0);
			HSSFRow row = st.getRow(0);
			if(row!=null){
				col = row.getLastCellNum();
			}
		}
		in.close();
		return col;
	}
	
	/**
	 * 获取列头数据
	 * @param file
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String getHeadCons(File file)throws FileNotFoundException, IOException{
		StringBuffer headCons = new StringBuffer();
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		if(wb.getNumberOfSheets()>0){
			int col = 0;
			HSSFSheet st = wb.getSheetAt(0);
			HSSFRow row = st.getRow(0);
			if(row!=null){
				col = row.getLastCellNum();
			}
			if(col>0){
				for (int columnIndex = 0; columnIndex < col; columnIndex++) {
					HSSFCell cell = row.getCell(columnIndex);
					headCons.append(getCellValue(cell));
				}
			}
		}
		in.close();
		return headCons.toString();
	}
	/**
	 * 获取列头数据
	 * @param file
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String[] getHeads(File file)throws FileNotFoundException, IOException{
		String[] headCons=null;
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		if(wb.getNumberOfSheets()>0){
			int col = 0;
			HSSFSheet st = wb.getSheetAt(0);
			HSSFRow row = st.getRow(0);
			if(row!=null){
				col = row.getLastCellNum();
			}
			if(col>0){
				headCons = new String[col];
				for (int columnIndex = 0; columnIndex < col; columnIndex++) {
					HSSFCell cell = row.getCell(columnIndex);
					headCons[columnIndex] = getCellValue(cell);
				}
			}
		}
		in.close();
		return headCons;
	}	
	public static String[][] ExcelDataToJson2(File file,String ignore_cols) {
		InputStream is;
		try {
			is = new FileInputStream(file);
			return ExcelDataToJson2(is,ignore_cols);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static String[][] ExcelDataToJson2(InputStream is,String ignore_cols) {
		return getExcelData2(is,ignore_cols);
	}
	
	public static String getCellValue(HSSFCell cell){
		String result="";
		try {
	        if (cell != null) {
	            switch (cell.getCellType()) { 
	            case HSSFCell.CELL_TYPE_STRING:
	            	result = cell.getStringCellValue();
					break;
	            case HSSFCell.CELL_TYPE_BLANK:
					break;
				case HSSFCell.CELL_TYPE_ERROR:
					result = "";
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						if (date != null) {
							result = new SimpleDateFormat("yyyy-MM-dd").format(date);
						} else {
							result = "";
						}
					} else {
						//result = cell.getNumericCellValue()+"";
						cell.setCellType(1);
						result = cell.getStringCellValue();
					}
					break;
				case HSSFCell.CELL_TYPE_FORMULA:
					// 导入时如果为公式生成的数据则无值
					if (!cell.getStringCellValue().equals("")) {
						result = cell.getStringCellValue();
					} else {
						result = cell.getNumericCellValue() + "";
					}
					break;
				default:
					cell.setCellType(1);
					result = cell.getStringCellValue();
					break;
				}
	        }
	    } catch(Exception e) {
	        e.printStackTrace();
	    }
		return result;
	}
	
	public static String[][] getExcelData2(InputStream is,String ignore_cols) {
		if (is == null) {
			return null;
		}
		String[][] data = null;
		try {
			HSSFWorkbook book = new HSSFWorkbook(is);
			HSSFSheet sheet = book.getSheetAt(0);
			int rows = sheet.getLastRowNum();
			int cols = 0;
			for (int i = 0; i < rows; i++) {
				if(i == 0) {
					HSSFRow row1 = sheet.getRow(i);
					cols = row1.getLastCellNum();
					data = new String[rows][cols];
				}
				HSSFRow row = sheet.getRow(i+1);
				for (int j = 0; j < cols; j++) {
					if(ignore_cols.indexOf("col"+j)>-1){
						data[i][j] = "";
					}else{
						HSSFCell cell = row.getCell(j);
						data[i][j] = getCellValue(cell);
					}
				}
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return data;
	}
}
